/**
 * 
 */
package com.tanker.common.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * @author jing.huang
 * @function
 * @date 2016年9月23日
 * @version 
 */
public class ExcelUtil {
	
	public static void main(String[] args) {
		String path = "D:/d.xlsx";
		System.out.println("*********************************************");
		String city="320600";//城市编号
		//是否插入表里
		boolean flag=true;
//		boolean flag=false;
//		Integer m=disCouponMemService.handleLimitReductionAmt(6551l, 56);
		System.out.println("*********************************************");
		List<Map<String,Object>> list=null;
//		param_area    `business`
//		set<String> areaS=new ArrayList<String>();
		Set<String> areaS=new HashSet<String>();
//		String path = "D:/凹凸租车-数据提取-160923.xlsx";
		
		try {
		   String postfix = ExcelUtil.getPostfix(path);
	          if (!"".equals(postfix)) {
	              if ("xls".equals(postfix)) {
	            	  list= ExcelUtil.readXlsBusines(path,areaS);
	              } else if ("xlsx".equals(postfix)) {
	            	  list= ExcelUtil.readXlsxBusiness(path,areaS);
	              }
	          } else {
	        	  System.out.println("商圈导入格式不对postfix={}");
	          }
			
		} catch (Exception e) {
      	  System.out.println("商圈导入格式不对postfix={}");
		}
		System.err.println(list);
		System.err.println(list.size());
		
	}
	//获取文件后缀
	public static String getPostfix(String path) {
	     if (path.contains(".")) {
	         return path.substring(path.lastIndexOf(".") + 1, path.length());
	     }
	     return "";
	}
	
	//读取文件
	public static List<Map<String,Object>> readExcel(String path) throws IOException {
          String postfix = getPostfix(path);
          if (!"".equals(postfix)) {
              if ("xls".equals(postfix)) {
                  return readXls(path);
              } else if ("xlsx".equals(postfix)) {
                  return readXlsx(path);
              }
          } else {
              System.out.println(path + "Not the Excel file");
          }
          return null;
      }
		  
      /**
       * Read the Excel 2010
       * @param path the path of the excel file
       * @return
       * @throws IOException
       */
      public static List<Map<String,Object>> readXlsx(String path) throws IOException {
          System.out.println("Processing..." + path);
          InputStream is = new FileInputStream(path);
          XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
          Map<String,Object> map = null;
          List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
          // Read the Sheet
          for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
              XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
              if (xssfSheet == null) {
                  continue;
              }
              // Read the Row
              for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                  XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                  if (xssfRow != null) {
                	  map = new HashMap<String, Object>();
                      XSSFCell mobile = xssfRow.getCell(0);
                      XSSFCell frameNo = xssfRow.getCell(1);
                      XSSFCell type = xssfRow.getCell(2);
                      XSSFCell crop1 = xssfRow.getCell(3);
                      XSSFCell crop2 = xssfRow.getCell(4);
                      map.put("mobile", getValue(mobile));
                      map.put("frameNo", getValue(frameNo));
                      map.put("type", getValue(type));
                      if(crop1 != null){
                    	  map.put("crop1", getValue(crop1));
                      }else{
                    	  map.put("crop1", "");
                      }
                      if(crop2 != null){
                    	  map.put("crop2", getValue(crop2));
                      }else{
                    	  map.put("crop2", "");
                      }
                      list.add(map);
                  }
              }
          }
          return list;
      }
      /**导入商圈
       * Read the Excel 2010
       * @param path the path of the excel file
     * @param areaS 
       * @return
       * @throws IOException
       */
      public static List<Map<String,Object>> readXlsxBusiness(String path, Set<String> areaS) throws IOException {
    	  System.out.println("Processing..." + path);
    	  InputStream is = new FileInputStream(path);
    	  XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
    	  Map<String,Object> map = null;
    	  List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
    	  // Read the Sheet
    	  for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
    		  XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
    		  if (xssfSheet == null) {
    			  continue;
    		  }
    		  // Read the Row
    		  for (int rowNum = 1871; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
    			  XSSFRow xssfRow = xssfSheet.getRow(rowNum);
    			  if (xssfRow != null) {
    				  map = new HashMap<String, Object>();
    				  XSSFCell area = xssfRow.getCell(0);
    				  XSSFCell bisName = xssfRow.getCell(1);
//    				  XSSFCell Name = xssfRow.getCell(2);
    				/*  XSSFCell type = xssfRow.getCell(2);
    				  XSSFCell crop1 = xssfRow.getCell(3);
    				  XSSFCell crop2 = xssfRow.getCell(4);*/
//    				  areaS.add(getValue(area));
    				  if (area!=null) {
    					  map.put("id", getValue(area));
						
					}
    				  map.put("index", rowNum);
    				  if (bisName!=null) {
        				  map.put("bisName", getValue(bisName));
    					  
    				  }
//    				  map.put("bis", getValue(Name));
    				  list.add(map);
    			  }
    		  }
    	  }
    	  return list;
      }
		  
      /**
       * Read the Excel 2003-2007
       * @param path the path of the Excel
       * @return
       * @throws IOException
       */
      public static List<Map<String,Object>> readXls(String path) throws IOException {
         System.out.println("Processing..." + path);
         InputStream is = new FileInputStream(path);
         HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
         Map<String,Object> map = null;
         List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
         // Read the Sheet
         for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
             HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
             if (hssfSheet == null) {
                 continue;
             }
             // Read the Row
             for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                 HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                 if (hssfRow != null) {
                     map = new HashMap<String, Object>();
                     HSSFCell mobile = hssfRow.getCell(0);
                     HSSFCell frameNo = hssfRow.getCell(1);
                     HSSFCell type = hssfRow.getCell(2);
                     HSSFCell crop1 = hssfRow.getCell(3);
                     HSSFCell crop2 = hssfRow.getCell(4);
                     map.put("mobile", getValue(mobile));
                     map.put("frameNo", getValue(frameNo));
                     map.put("type", getValue(type));
                     map.put("crop1", getValue(crop1));
                     map.put("crop2", getValue(crop2));
                     list.add(map);
                 }
             }
         }
         return list;
     }
	 
      /**
       * Read the Excel 2003-2007
       * @param path the path of the Excel
     * @param areaS 
       * @return
       * @throws IOException
       */
      public static List<Map<String,Object>> readXlsBusines(String path, Set<String> areaS) throws IOException {
    	  System.out.println("Processing..." + path);
    	  InputStream is = new FileInputStream(path);
    	  HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
    	  Map<String,Object> map = null;
    	  List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
    	  // Read the Sheet
    	  for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
    		  HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
    		  if (hssfSheet == null) {
    			  continue;
    		  }
    		  // Read the Row
    		  for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
    			  HSSFRow hssfRow = hssfSheet.getRow(rowNum);
    			  if (hssfRow != null) {
    				  map = new HashMap<String, Object>();
    				  map = new HashMap<String, Object>();
    				  HSSFCell area = hssfRow.getCell(0);
    				  HSSFCell bisName = hssfRow.getCell(1);
    				/*  XSSFCell type = xssfRow.getCell(2);
    				  XSSFCell crop1 = xssfRow.getCell(3);
    				  XSSFCell crop2 = xssfRow.getCell(4);*/
    				  areaS.add(getValue(area));
    				  map.put("area", getValue(area));
    				  map.put("bisName", getValue(bisName));
    				  list.add(map);
    				  list.add(map);
    			  }
    		  }
    	  }
    	  return list;
      }
      
     @SuppressWarnings("static-access")
     private static String getValue(XSSFCell xssfRow) {
         if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
             return String.valueOf(xssfRow.getBooleanCellValue());
         } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
        	 DecimalFormat df = new DecimalFormat("0");  
        	 String whatYourWant = df.format(xssfRow.getNumericCellValue());  
//             return String.valueOf(xssfRow.getNumericCellValue());
        	 return whatYourWant;
         } else {
             return String.valueOf(xssfRow.getStringCellValue());
         }
     }
     
     @SuppressWarnings("static-access")
     private static String getValue(HSSFCell hssfCell) {
         if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
             return String.valueOf(hssfCell.getBooleanCellValue());
         } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
             return String.valueOf(hssfCell.getNumericCellValue());
         } else {
             return String.valueOf(hssfCell.getStringCellValue());
         }
     }
     
   
  
}
